package Database;

import java.sql.*;
import java.util.Locale;
import java.util.Vector;

public class TaiKhoan 
{
    private String username;
    private String password;
    private String hoten;
    private String quyen;

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * @return the hoten
     */
    public String getHoten() {
        return hoten;
    }

    /**
     * @param hoten the hoten to set
     */
    public void setHoten(String hoten) {
        this.hoten = hoten;
    }

    /**
     * @return the quyen
     */
    public String getQuyen() {
        return quyen;
    }

    /**
     * @param quyen the quyen to set
     */
    public void setQuyen(String quyen) {
        this.quyen = quyen;
    }
    
    public boolean KiemTraTaiKhoan(String username, String password)
    {
        boolean kq = true;
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan where username=? and password=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            int dem = 0;
            while(rs.next())
            {
                dem++;
            }
            kq = dem==1;
        }
        catch(Exception ex)
        {
            kq = false;
        }
        
        return kq;        
    }
    
    /**
     * Them tai khoan moi bang cach su dung store procedure
     * @return boolean
     */
    public boolean ThemTaiKhoan()
    {
        boolean kq = true;
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            CallableStatement cs = con.prepareCall("{call sp_themtk(?,?,?,?)}");
            cs.setString(1, this.username);
            cs.setString(2, this.password);
            cs.setString(3, this.hoten);
            cs.setString(4, this.quyen);
            kq = cs.executeUpdate() > 0;
        }
        catch(Exception ex)
        {
            kq = false;
        }
        
        return kq;
    }
    
    public void HienThi()
    {
        System.out.println("Username: " + this.username);
        System.out.println("Password: " + this.password);
        System.out.println("Ho ten: " + this.hoten);
        System.out.println("Quyen: " + this.quyen);
    }
    
    public void DungResultSet()
    {
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            ResultSet rs = st.executeQuery(query);

            System.out.println("Tai khoan dau tien");
            rs.first();
            System.out.println(rs.getString("username"));
            
            System.out.println("Tai khoan cuoi cung");
            rs.last();
            System.out.println(rs.getString("username"));
            
            System.out.println("Tai khoan thu 1");
            rs.absolute(1);
            System.out.println(rs.getString("username"));
            
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
    }
    
    /**
     * Cap nhat du lieu bang result set
     */
    public void CapNhatDuLieu()
    {
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(query);
            
            // Cap nhat password cho tai khoan dau tien
            rs.first();
            rs.updateString("password", "heeee");
            rs.updateRow();
            
            // Cap nhat du lieu o dong bat ki
            rs.absolute(2);
            rs.updateString("hoten", "abc");
            rs.updateString("password", "aaaa");
            rs.updateString("quyen", "admin");
            rs.updateRow();
            
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
    }
    
    /**
     * Them tai khoan tu result set
     */
    public void ThemTaiKhoanRS()
    {
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(query);
            
            rs.moveToInsertRow();
            rs.updateString("username", "nguyenvana");
            rs.updateString("password", "111111");
            rs.updateString("hoten", "Nguyen Van A");
            rs.updateString("quyen", "admin");
            rs.insertRow();
            
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
    }
    
    public void XoaTaiKhoanRS()
    {
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(query);
            rs.last();
            rs.deleteRow();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
    }
    
    public static Vector<Vector<Object>> DanhSachTaiKhoan()
    {
        Vector<Vector<Object>> dstk = new Vector<Vector<Object>>();
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Vector<Object> tk = new Vector<Object>();
                tk.add(rs.getString("username"));
                tk.add(rs.getString("password"));
                tk.add(rs.getString("hoten"));
                tk.add(rs.getString("quyen"));
                dstk.add(tk);
            }
        }
        catch(Exception ex)
        {
            dstk = null;
        }
        
        return dstk;
    }
    
    public TaiKhoan ThongTinTaiKhoan(String username)
    {
        TaiKhoan tk = new TaiKhoan();
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan where username=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                tk.setUsername(rs.getString("username"));
                tk.setPassword(rs.getString("password"));
                tk.setHoten(rs.getString("hoten"));
                tk.setQuyen(rs.getString("quyen"));
            }
        }
        catch(Exception ex)
        {
            tk = null;
        }
        
        return tk;
    }
    
    public boolean CapNhatTaiKhoan(int selectrow)
    {
        boolean kq = true;
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "select * from taikhoan";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(query);
            rs.absolute(selectrow);
            rs.updateString("password", this.password);
            rs.updateString("hoten", this.hoten);
            rs.updateString("quyen", this.quyen);
            rs.updateRow();            
        }
        catch(Exception ex)
        {
            kq = false;
        }
        
        return kq;
    }
    
    public boolean XoaTaiKhoan(String username)
    {
        boolean kq = true;
        
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "delete from taikhoan where username=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, username);
            kq = ps.executeUpdate() > 0;
        }
        catch(Exception ex)
        {
            kq = false;
        }
        
        return kq;
    }
    
    public boolean ThemTK()
    {
        boolean kq = true;
        try
        {
            Connection con = ConnectToDatabase.getConnection();
            String query = "insert into taikhoan values(?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, this.username);
            ps.setString(2, this.password);
            ps.setString(3, this.hoten);
            ps.setString(4, this.quyen);
            kq = ps.executeUpdate() > 0;
        }
        catch(Exception ex)
        {
            kq = false;
        }
        return kq;
    }
    
}
